package tom.server;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.util.JRLoader;

import tom.client.Service;
import tom.shared.AppUser;
import tom.shared.Company;
import tom.shared.LookupRecord;

public class ServiceImpl extends SessionService implements Service {

	private static final long serialVersionUID = 4490260858717517520L;

	public static final TimeZone zone = TimeZone.getTimeZone("GMT+7");

	public long getServerTime() {
		return System.currentTimeMillis() + zone.getRawOffset();
	}

	public Integer verify(String pgmId, String user, String password) {
		Database dbConn = this.getDatabase();
		// String comCode = null;
		boolean isAuthen = false;
		Integer level = -1;
		if (dbConn != null) {
			try {
				ResultSet rs = dbConn
						.executeQuery("select passwd2, comCode, level from appuser where username='"
								+ user + "'");
				String storePasswd = null;
				if (rs.next()) {
					storePasswd = rs.getString(1);
					// algorithm.update(password.getBytes());
					// byte messageDigest[] = algorithm.digest();
					// String passwordMD5 = new String(messageDigest);
					level = rs.getInt(3);
					isAuthen = storePasswd == null || password == null
							|| storePasswd.equals(password);

					if (isAuthen && level < 9) {
						// level = rs.getInt(3);
						rs = dbConn
								.executeQuery("select level from permission where username='"
										+ user
										+ "' and pgmId='"
										+ pgmId
										+ "' and comCode='"
										+ getSessVar("comCode") + "'");
						if (rs.next())
							level = rs.getInt(1);
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return level;
	}

	public AppUser login(String user, String password) {
		final String confile = "webapps"
				+ this.getSession().getServletContext().getContextPath()
				+ "/db.conf";
		this.setDbName(Database.readConnectURL(confile));
		Database dbConn = Database.connectDatabase(dbName, "admin", "tommy");
		String comCode = null;
		boolean isAuthen = false;
		String level = null;
		if (dbConn != null) {
			try {
				ResultSet rs = dbConn
						.executeQuery("select passwd, comCode, level from appuser where username='"
								+ user + "'");
				String storePasswd = null;
				if (rs.next()) {
					storePasswd = rs.getString(1);
					// algorithm.update(password.getBytes());
					// byte messageDigest[] = algorithm.digest();
					// String passwordMD5 = new String(messageDigest);
					isAuthen = storePasswd == null
							|| storePasswd.equals(password);
					if (isAuthen) {
						comCode = rs.getString("comCode");
						level = rs.getString("level");
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		String logMessage = getThreadLocalRequest().getRemoteAddr()
				+ " login [" + user + "] ";

		AppUser record = null;
		if (isAuthen) {
			String sql = "select * from appuser where username='" + user + "'";
			ResultSet res = dbConn.executeQuery(sql);
			try {
				if (res.next())
					record = new AppUser(res.getString(1), res.getString(2),
							res.getString(3), res.getDate(4), res.getString(5),
							res.getString(6), res.getInt(7));
			} catch (SQLException e) {
				e.printStackTrace();
			}
			getSession().setAttribute("user", user);
			getSession().setAttribute("loginTime", new Date());
			getSession().setAttribute(dbName, dbConn);
			getSession().setAttribute("comCode", comCode);
			getSession().setAttribute("level", level);
			log(logMessage + "OK"); // linux:/var/lib/tomcat6/logs/localhost.YYYY-MM-DD.log
		} else {
			log(logMessage + " Fail!");
			logout();
		}
		return record;
	}

	public void logout() {
		Database conn = this.getDatabase();
		if (conn != null) {
			conn.close();
			getSession().removeAttribute(dbName);
			getSession().removeAttribute("user");
			getSession().removeAttribute("loginTime");
			getSession().removeAttribute("comCode");
			getSession().removeAttribute("level");
			this.getServletContext().removeAttribute(getSession().getId());
			final String pdfFile = "webapps"
					+ this.getSession().getServletContext().getContextPath()
					+ "/output/" + getSession().getId() + ".pdf";
			File f = new File(pdfFile);
			f.delete();
		}
	}

	public String[][] getPermissionForm() {
		String user = getSessVar("user");
		String comCode = getSessVar("comCode");
		String level = getSessVar("level");
		List<Object[]> list = null;
		String[][] arr = null;
		if (user != null && !user.equals("")) {
			StringBuffer sql = new StringBuffer();
			sql.append("select pgmid, pgmname, module, descript, active ");
			if (level.equals("9")) {
				sql.append("from program ");
			} else {
				sql.append("from program where pgmid in ");
				sql.append("(select pgmid from permission where username='"
						+ user + "' and comCode='" + comCode + "') ");
			}
			sql.append("order by module, pgmid");

			list = super.executeQuery(sql.toString());
			arr = toStringArray(list);
		} else
			arr = new String[0][2];
		return arr;
	}

	public Company getCompany() {
		String comCode = this.getSessVar("comCode");
		String sql = "select * from company where comcode = '" + comCode + "'";
		ResultSet result = this.getDatabase().executeQuery(sql);
		Company com = null;
		try {
			if (result.next()) {
				com = new Company();
				com.setComCode(result.getString(1));
				com.setName(result.getString(2));
				com.setAddress(result.getString(3));
				com.setTelephone(result.getString(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return com;
	}

	private boolean countPrint(String jasperFile, Map<String, Object> parameters) {
		boolean canPrint = true;
		int count = 0;
		Database db = super.getDatabase();
		ResultSet rs = null;
		Object reprint = parameters.get("reprint");
		if (jasperFile.equals("report004.jasper")
				|| jasperFile.equals("report005.jasper")
				|| jasperFile.equals("report006.jasper")) {
			rs = db.executeQuery("select printCounter from invoice where comCode='"
					+ parameters.get("comCode")
					+ "' and invNo="
					+ parameters.get("invNo"));
			try {
				if (rs.next())
					count = rs.getInt("printCounter");
			} catch (SQLException e) {
				e.printStackTrace();
			}
			canPrint = reprint != null || count == 0;
			if (canPrint)
				db.executeUpdate("update invoice set printCounter=printCounter+1 where comCode='"
						+ parameters.get("comCode")
						+ "' and invNo="
						+ parameters.get("invNo"));
		}
		return canPrint;
	}

	public String fillReport(String jasperFile, Map<String, Object> parameters) {
		String sessionReport = this.getSession().getId();
		System.out.println("Fill Report ************");
		final String reportFolder = "webapps"
				+ this.getSession().getServletContext().getContextPath()
				+ "/report/";
		final String outFolder = "webapps"
				+ this.getSession().getServletContext().getContextPath()
				+ "/output/";
		if (countPrint(jasperFile, parameters)) {
			JasperPrint jasperPrint = null;
			File f = new File(reportFolder + this.getSessVar("comCode") + "/"
					+ jasperFile);
			if (!f.exists())
				f = new File(reportFolder + jasperFile);
			System.out.println(f.toString());
			try {
				// JasperReport jasperReport = (JasperReport) JRLoader
				// .loadObjectFromFile(reportFolder + jasperFile);
				JasperReport jasperReport = (JasperReport) JRLoader
						.loadObjectFromFile(f.toString());

				jasperPrint = JasperFillManager.fillReport(jasperReport,
						parameters, getDatabase().getConnection());
				JasperExportManager.exportReportToPdfFile(jasperPrint,
						outFolder + sessionReport + ".pdf");
				// JRSaver.saveObject(jasperPrint, outFolder +
				// "report.jrprint");
			} catch (JRException e) {
				log("jasper = " + e.getMessage());
			}

			// getServletContext().setAttribute(sessionReport, jasperPrint);
		} else {
			// getServletContext().setAttribute(sessionReport, null);
			File f = new File(outFolder + sessionReport + ".pdf");
			f.delete();
		}
		return sessionReport;
	}

	public Date getDeployDate() {
		File warFile = new File("webapps/windmill.war");
		Date d = null;
		if (warFile.exists()) {
			d = new Date(warFile.lastModified());
		}
		return d;
	}

	// public String lookupField(String fName, Object fValue) {
	// Database db = this.getDatabase();
	// ResultSet rs = db.querySqlMap(fName, new Object[] { fValue });
	// String displayField = null;
	// try {
	// if (rs != null && rs.next()) {
	// displayField = rs.getString(1);
	// }
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	// return displayField;
	// }

	public List<LookupRecord> lookupList(String keyFieldName,
			String lkFieldValue) {
		if (this.getDatabase().getStatement(keyFieldName) == null) {
			String sql = lookupQuery(keyFieldName);
			sql = sql + " limit 500";
			this.getDatabase().createSQL(keyFieldName, sql);
		}

		if (!keyFieldName.equals("invSaleDate")) {
			if (lkFieldValue == null || lkFieldValue.equals(""))
				lkFieldValue = "%";
			else if (lkFieldValue.indexOf("%") < 0)
				lkFieldValue = "%" + lkFieldValue + "%";
		}

		Object[] params = lookupParams(keyFieldName, lkFieldValue);

		List<LookupRecord> list = new ArrayList<LookupRecord>();
		try {
			ResultSet res = getDatabase().querySqlMap(keyFieldName, params);
			LookupRecord record = null;

			while (res.next()) {
				record = new LookupRecord();
				record.key = res.getString(1);
				record.name = res.getString(2);
				list.add(record);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public Object[] lookupParams(String keyFieldName, String lkFieldValue) {
		// System.out.println("Params = " + keyFieldName + " : " +
		// lkFieldValue);
		Object[] params = { lkFieldValue };
		if (keyFieldName.equals("proCode")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		}
		if (keyFieldName.equals("prjCode")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("resCode")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("grpId")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("postCode")) {
			params = new Object[3];
			params[0] = lkFieldValue;
			params[1] = lkFieldValue;
			params[2] = lkFieldValue;
		} else if (keyFieldName.equals("contCode")) {
			params = new Object[3];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
			params[2] = lkFieldValue;
		} else if (keyFieldName.equals("accCode")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("bkCode")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("invNo")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("invSaleDate")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;

			// if (lkFieldValue == null || lkFieldValue.equals("%"))
			// params[1] = null;
			// else
			// params[1] = lkFieldValue;
			// params[1] = Library.DATE_FORMAT.parse(lkFieldValue);
			// int i = 0;
			// for (Object obj : params) {
			// System.out.println(keyFieldName + " : " + i + " = "
			// + obj.toString());
			// i = i + 1;
			// }
		} else if (keyFieldName.equals("dueDay")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("stCode")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("vatCode")) {
			params = new Object[0];
		} else if (keyFieldName.equals("payMeth")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("voucherNo")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("proGrp")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		} else if (keyFieldName.equals("unitCode")) {
			params = new Object[2];
			params[0] = super.getSessVar("comCode");
			params[1] = lkFieldValue;
		}
		return params;
	}

	public String lookupQuery(String keyFieldName) {
		String sql = null;
		if (keyFieldName.equals("comCode")) {
			sql = "select comcode as 'key', name as 'name' from company where name like ?";
		} else if (keyFieldName.equals("pgmId")) {
			sql = "select pgmid as 'key', pgmname as 'name' from program where pgmName like ?";
		} else if (keyFieldName.equals("userName")) {
			sql = "select username as 'key', fullname as 'name' from appuser where fullname like ?";
		} else if (keyFieldName.equals("proCode")) {
			sql = "select proCode as 'key', name as 'name' from product where comCode=? and active=true and name like ?";
		} else if (keyFieldName.equals("grpId")) {
			sql = "select grpId as 'key', grpName as 'name' from contactgroup where comCode=? and grpName like ?";
		} else if (keyFieldName.equals("postCode")) {
			sql = "select id as 'key', concat(postCode, ', ', amphur, ', ' , province) as 'name' "
					+ "from postcode where postCode like ? or amphur like ? or province like ?";
		} else if (keyFieldName.equals("contCode")) {
			sql = "select contCode as 'key', concat(ifnull(name,' '),' ',ifnull(organiz,' ')) as 'name' "
					+ "from contact where active=true and comCode=? and (name like ? or organiz like ?) order by contCode desc";
		} else if (keyFieldName.equals("accCode")) {
			sql = "select accCode as 'key', descript as 'name' "
					+ "from account where comCode=? and descript like ?";
		} else if (keyFieldName.equals("bkCode")) {
			sql = "select bkCode as 'key', descript as 'name' "
					+ "from accbook where comCode=? and descript like ?";
		} else if (keyFieldName.equals("invNo")) {
			// sql =
			// "select invNo as 'key', concat(systemKind, cast(date(invDate) as char), ' [', cast(totalAmt as char), ']', ifnull(name,' '),' ',ifnull(organiz,' ')) as 'name' "
			// +
			// "from invoice, contact where invoice.comCode=? and contact.comCode=invoice.comCode "
			// +
			// "and invoice.contCode=contact.contCode and (contact.name like ? or organiz like ?) "
			// + "order by invDate";
			sql = "select invNo as 'key', concat(systemKind, docRef, ' [', cast(totalAmt as char), ']', ifnull(contactAddr, ' ')"
					+ ") as 'name' "
					+ "from invoice where invoice.comCode=? "
					+ "and contactAddr like ? or contactAddr is null "
					+ "order by invNo desc";
		} else if (keyFieldName.equals("resCode")) {
			sql = "select resCode as 'key', descript as 'name' from rreason where comCode=? and descript like ?";
		} else if (keyFieldName.equals("prjCode")) {
			sql = "select prjCode as 'key', descript as 'name' from project where comCode=? and descript like ?";
		} else if (keyFieldName.equals("invSaleDate")) {
			sql = "select invNo as 'key', concat(systemKind, cast(date(invDate) as char), '[', cast(totalAmt as char), ']', ifnull(name,' '),' ',ifnull(organiz,' ')) as 'name' "
					+ "from invoice, contact where invoice.comCode=?  and contact.comCode=invoice.comCode "
					+ "and invoice.contCode=contact.contCode and cast(date(invDate) as char)=?";
		} else if (keyFieldName.equals("dueDay")) {
			sql = "select day as 'key', descript as 'name' from dueday where comCode=? and descript like ?";
		} else if (keyFieldName.equals("stCode")) {
			sql = "select stCode as 'key', descript as 'name' from contstat where comCode=? and descript like ?";
		} else if (keyFieldName.equals("vatCode")) {
			sql = "select vatCode as 'key', rate as 'name' from vat";
		} else if (keyFieldName.equals("payMeth")) {
			sql = "select paymethCode as 'key', paymentMethod as 'name' from paymeth where comCode=? and paymentMethod like ?";
		} else if (keyFieldName.equals("voucherNo")) {
			sql = "select voucherNo as 'key', concat(' [', cast(totalAmt as char), ']', ifnull(contAddr, ' ')"
					+ ") as 'name' "
					+ "from voucher where comCode=? and contAddr like ?";
		} else if (keyFieldName.equals("proGrp")) {
			sql = "select proGrp as 'key', descript as 'name' from prodgroup where comCode=? and descript like ?";
		} else if (keyFieldName.equals("unitCode")) {
			sql = "select name as 'key', name as 'name' from unitlist where comCode=? and name like ?";
		}
		return sql;
	}

	@Override
	public String[][] getWorkingCompany() {
		String user = getSessVar("user");
		String level = getSessVar("level");
		StringBuffer sql = new StringBuffer();
		sql.append("select comcode, name from company ");
		if (!level.equals("9")) {
			sql.append("where comcode in ");
			sql.append("(select comcode from working where username='" + user
					+ "')");
		}
		sql.append("order by name");

		List<Object[]> list = super.executeQuery(sql.toString());
		return toStringArray(list);
	}

}
